#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# 文件名: clean_firstRow.py
# 作者: ajie
# 创建时间: 2025-07-16 13:46
# 描述: ...


import openpyxl
import pathlib

class ExcelFirstRowCleaner:
    def __init__(self, path):
        """:param path: 文件路径或目录路径"""
        self.path = pathlib.Path(path)
    
    def clean_file(self, file_path):
        """清理单个Excel文件的首行"""
        try:
            wb = openpyxl.load_workbook(file_path)
            modified = False
            
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                first_row = [cell.value for cell in ws[1]]
                
                # 检查第一行是否全为空
                if all(cell is None or str(cell).strip() == "" for cell in first_row):
                    # 保存合并单元格信息
                    merged_ranges = list(ws.merged_cells.ranges)
                    ws.delete_rows(1)
                    print(f"已删除 {sheet_name} 的第一行")
                    
                    # 更新合并单元格
                    for mr in merged_ranges:
                        min_row, max_row = mr.min_row, mr.max_row
                        # 跳过完全在第一行的合并
                        if min_row == 1 and max_row == 1:
                            continue
                        # 调整合并区域行号
                        new_min_row = max(min_row - 1, 1)
                        new_max_row = max(max_row - 1, 1)
                        if new_min_row <= new_max_row:
                            col_start = openpyxl.utils.get_column_letter(mr.min_col)
                            col_end = openpyxl.utils.get_column_letter(mr.max_col)
                            new_range = f"{col_start}{new_min_row}:{col_end}{new_max_row}"
                            ws.merge_cells(new_range)
                    
                    modified = True
            
            if modified:
                wb.save(file_path)
                print(f"{file_path} 处理完成")
            else:
                print(f"{file_path} 无需修改")
                
        except Exception as e:
            print(f"{file_path} 处理失败: {e}")
    
    def process(self):
        """处理路径（文件或目录）"""
        if self.path.is_file() and self.path.suffix.lower() in ('.xlsx', '.xls'):
            self.clean_file(str(self.path))
        elif self.path.is_dir():
            for file in self.path.rglob("*"):
                if file.suffix.lower() in ('.xlsx', '.xls'):
                    self.clean_file(str(file))
        else:
            print("错误：路径必须是有效的Excel文件或目录")


if __name__ == "__main__":
    cleaner = ExcelFirstRowCleaner(r"E:\行业标准\two\订单（订单评审表）")
    cleaner.process()
